package com.codeoss.framework.impexp;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.codeoss.framework.context.YunContext;
import com.codeoss.framework.converter.IConverterStore;
import com.codeoss.framework.data.metadata.Column;
import com.codeoss.framework.data.metadata.Query;
import com.codeoss.framework.data.metadata.Restrictions;
import com.codeoss.framework.data.metadata.Table;
import com.codeoss.framework.data.metadata.TableData;
import com.codeoss.framework.data.store.ITableStore;
import com.codeoss.framework.util.DictDef;
import com.codeoss.framework.util.IOUtil;
import com.codeoss.framework.util.LookupInfo;
import com.codeoss.framework.util.StringUtils;
import com.codeoss.framework.util.ValueNamePair;
import com.codeoss.framework.view.datasource.DataSourceConfig;
import com.codeoss.framework.view.datasource.IDataSource;
import com.codeoss.framework.view.datasource.store.IDataSourceStore;
import com.codeoss.module.ir.service.IIrDictService;

@Service
public class ExcelImport implements IExcelImport {
	@Autowired
    private ITableStore tableStore;
    @Autowired
    private IIrDictService irDictService;
    @Autowired
    private IDataSourceStore dataSourceStore;
    @Autowired
    private IConverterStore converterStore;
	@Override
	@Transactional
	public void importExcel(YunContext yunContext, Table table, InputStream is) throws IOException {
		// TODO Auto-generated method stub
		Workbook workbook=null;
		try {
			workbook=new XSSFWorkbook(is);
			Sheet sheet = workbook.getSheetAt(0);
			int rowEnd = sheet.getPhysicalNumberOfRows();
			Row headRow=sheet.getRow(0);
			short lastCellNum=headRow.getLastCellNum();
			List<Column> columnList=table.getAllColumnList();
			for(int i=1;i<rowEnd;i++) {
				Row row=sheet.getRow(i);
				Map<String,Object> dataMap=new HashMap<String,Object>();
				for(short j=0;j<lastCellNum;j++) {
					String name=headRow.getCell(j).getStringCellValue();
					name=name.trim();
					String sValue=getCellStringValue(row.getCell(j));
					if(!StringUtils.isEmpty(sValue)) {
						Column column=null;
						for(int k=0;k<columnList.size();k++) {
							if(columnList.get(k).getName().equals(name)) {
								column=columnList.get(k);
								break;
							}
						}
						if(column==null) {
							throw new RuntimeException("没找到"+name+"列");
						}
						LookupInfo lookupInfo=column.getLookupInfo();
						if(lookupInfo!=null) {
							DictDef dictDef=lookupInfo.getDictDef();
							if(dictDef!=null) {
								List<ValueNamePair> pairList=irDictService.getPairListByCode(dictDef.getRefCode());
								for(int k=0;k<pairList.size();k++) {
									if(pairList.get(k).getName().equals(sValue)) {
										dataMap.put(column.getColumnName(), pairList.get(k).getValue());
									}
								}
							}else {
								DataSourceConfig dataSourceConfig=lookupInfo.getDataSourceConfig();
								if(dataSourceConfig!=null) {
									Table childTable=tableStore.get(dataSourceConfig.getTableName());
									IDataSource dataSource=dataSourceStore.getDataSource(childTable.getAccessType());
									Query query=new Query();
									query.add(Restrictions.eq(childTable.getCodeColumn().getColumnName(), sValue));
									TableData tableData=dataSource.select(yunContext, childTable, query);
									Long value=null;
									if(tableData.getTotal()>0){
										value=(Long)tableData.getData().get(0).get("id");
									}
									if(value==null) {
										query=new Query();
										query.add(Restrictions.eq(childTable.getNameColumn().getColumnName(), sValue));
										tableData=dataSource.select(yunContext, childTable, query);
										if(tableData.getTotal()>0){
											value=(Long)tableData.getData().get(0).get("id");
										}
									}
									dataMap.put(column.getColumnName(), value);
								}
							}
						}else {
							Object value=converterStore.getConverter(column.getDataType()).convertStringToObject(sValue);
							dataMap.put(column.getColumnName(), value);
						}
					}
				}
				DataSourceConfig dataSourceConfig=new DataSourceConfig();
				dataSourceConfig.setTableName(table.getTableName());
				IDataSource dataSource=dataSourceStore.getDataSource(table.getAccessType());
				dataSource.insert(yunContext, table, dataMap);
			}
			
		}catch (IOException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}finally {
			IOUtil.closeInputStream(is);
			try {
				if (workbook != null)
					workbook.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block

			}
		}
	}
	
	protected String getCellStringValue(Cell cell) {
		if(cell==null){
            return null;
        }
		String returnValue = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();
                returnValue=String.valueOf(doubleValue);
                break;
            case Cell.CELL_TYPE_STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case Cell.CELL_TYPE_BLANK:     // 空值
                break;
            case Cell.CELL_TYPE_FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
	}

}
